Accommodating Dimension Hierarchies in a Data Warehouse View/index Selection Scheme

Storing vast number of aggregate tables (materialized views) of the base data collected from its various independent data sources is one way warehousing systems provide fast access to data requested by complex warehouse queries. A data warehouse collects, stores and integrates large amounts of data from various function oriented databases over a long period of time which is used for online analytical processing (OLAP). In addition to storing views which project mostly on primary key attributes (e.g., customerid), materializing some of their indexes help reduce query response time at the expense of increasing maintenance cost for stored tables and diminishing storage space. Thus, in order to achieve near optimal query response time, maintenance cost and storage space utilization, schemes that enable careful selection of views and indexes are required. For an even better system performance, extending these selection schemes to accommodate views that are grouped on dimension attributes of subjects (e.g., customer name, phone) and not on their primary keys cuts oo the enormous amount of time spent performing joins with dimension tables. A data cube is an elegant way for representing information in a Warehouse and is an n-dimensional view with 2 n subviews. Most commercial OLAP systems today pick the summary tables and indexes to materialize in a trial-and-error manner. This paper extends a uniform technique for selecting subviews and indexes of a data cube to materialize, to also include selection of some dimension views. The objective is improve system performance by selecting the set of subviews (V) of the data cube, some of their dimension views (D), and some of their indexes (I) that have the best resultant query response time, maintenance cost taking into account their space usage and the warehouse query access pattern and usage.